﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using CommunitySystemModel;
using System.Data.SqlClient;
using System.Data;

namespace CommunitySystem.BO.SystemManager
{
    public class MenuManagerBO
    {
        #region 单例
        private static MenuManagerBO instance = null;
        // private string strConnection;
        private static AccessHelper acc = new AccessHelper();
        private MenuManagerBO()
        {
            //strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionDB"].ConnectionString;
        }

        public static MenuManagerBO GetInstance()
        {
            if (instance == null)
                instance = new MenuManagerBO();
            return instance;
        }
        #endregion

        public ObservableCollection<MenuItem> GetMenuItem(string condition)
        {
            ObservableCollection<MenuItem> menus = new ObservableCollection<MenuItem>();
            string sql = "select [ID], [Title], IsNull([Target], '') as Target, [Index], [PID], icon, IsDefault from MenuItem ";
            if (!string.IsNullOrEmpty(condition))
            {
                sql += "  where " + condition;
            }
            sql += " order by [index] ";
            DataSet ds = acc.ExcuteDataSet(sql);
            if (ds == null || ds.Tables.Count < 1)
                return null;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                MenuItem item = new MenuItem()
                {
                    ID = int.Parse(dr["ID"].ToString()),
                    Title = dr["Title"].ToString(),
                    Target = dr["Target"].ToString(),
                    Index = int.Parse(dr["Index"].ToString()),
                    PID = int.Parse(dr["PID"].ToString()),
                    icon = dr["icon"] as byte[],
                    IsDefault = Convert.ToBoolean(dr["IsDefault"].ToString())
                };
                menus.Add(item);

            } return menus;
        }
        public ObservableCollection<MenuItem> GetMenuItemByPID(int pid)
        {
            ObservableCollection<MenuItem> menus = new ObservableCollection<MenuItem>();
            string sql = string.Format(@"with Menu as(
	select ID, Title, Target, [index], PID, icon, IsDefault from MenuItem where pid={0}
	union all 
	select mi.ID, mi.Title, mi.Target, mi.[index], mi.PID, mi.icon, mi.IsDefault from Menu m 
	inner join MenuItem mi on mi.PID = m.id
)
select ID, Title, Isnull(Target, '') as [Target], [index], PID, icon, IsDefault from Menu  order by [index]", pid);
            DataSet ds = acc.ExcuteDataSet(sql);
            if (ds == null || ds.Tables.Count < 1)
                return null;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                MenuItem item = new MenuItem()
                {
                    ID = int.Parse(dr["ID"].ToString()),
                    Title = dr["Title"].ToString(),
                    Target = dr["Target"].ToString(),
                    Index = int.Parse(dr["Index"].ToString()),
                    PID = int.Parse(dr["PID"].ToString()),
                    icon = dr["icon"] as byte[],
                    IsDefault = Convert.ToBoolean(dr["IsDefault"].ToString())
                };
                menus.Add(item);

            }
            return menus;

            //SqlConnection conn = new SqlConnection(strConnection);
            //try
            //{
            //    conn.Open();
            //    SqlCommand cmand = new SqlCommand(sql, conn);

            //    SqlDataReader reader = cmand.ExecuteReader();
            //    while (reader.Read())
            //    {
            //        MenuItem item = new MenuItem();
            //        item.ID = reader.GetInt32(0);
            //        item.Title = reader.GetString(1);
            //        item.Target = reader.GetString(2);
            //        item.Index = reader.GetInt32(3);
            //        item.PID = reader.GetInt32(4);
            //        item.icon = reader["icon"] as byte[];
            //        item.IsDefault = reader.GetBoolean(6);
            //        menus.Add(item);
            //    }
            //    return menus;
            //}
            //finally
            //{
            //    conn.Close();
            //    conn.Dispose();
            //}
        }

        public void AddMenuItem(MenuItem item)
        {
            string sql = "insert into MenuItem(Title, Target, [Index], PID, [icon], IsDefault) values(@Title, @Target, @Index, @PID, @icon, @IsDefault)    select @@IDENTITY";
            SqlParameter[] parameters = {
					new SqlParameter("@Title", SqlDbType.VarChar,100),
					new SqlParameter("@Target", SqlDbType.VarChar,500),
					new SqlParameter("@Index", SqlDbType.Int,4),
					new SqlParameter("@PID", SqlDbType.Int,4),
					new SqlParameter("@icon", SqlDbType.Image),
					new SqlParameter("@IsDefault", SqlDbType.Bit,1)};
            parameters[0].Value = item.Title;
            parameters[1].Value = item.Target;
            parameters[2].Value = item.Index;
            parameters[3].Value = item.PID;
            parameters[4].Value = item.icon;
            parameters[5].Value = item.IsDefault;
            //List<SqlParameter> sqlparams = new List<SqlParameter>()
            //{
            //    new SqlParameter(){ParameterName="@Title", Value = item.Title },
            //    new SqlParameter(){ParameterName="@Target", Value = string.IsNullOrEmpty(item.Target)? "":item.Target},
            //    new SqlParameter(){ParameterName="@Index", Value = item.Index},
            //    new SqlParameter(){ParameterName="@PID", Value = item.PID},
            //    new SqlParameter(){ParameterName="@IsDefault", Value= item.IsDefault},

            //};
            //SqlParameter p = new SqlParameter() { 
            //    ParameterName = "@icon", Value = item.icon, SqlDbType = System.Data.SqlDbType.Image };
            //if (item.icon == null)
            //    p.Value = DBNull.Value;
            //sqlparams.Add(p);
            if (item.PID == 0 && item.IsDefault)//系统主页
            {
                //SqlConnection conn = null;
                //SqlTransaction transaction = AccessHelper.GetInstance().BeginTransaction(out conn);
                //SqlCommand cmmd = new SqlCommand(sql, conn, transaction);
                //cmmd.Parameters.AddRange(sqlparams.ToArray());
                //SqlDataReader reader = cmmd.ExecuteReader();
                //reader.Read();
                //reader.Close();
                object obj = acc.ExecuteScalar(sql, parameters);
                if (obj != null)
                    UpdateIsDefault(Convert.ToInt32(obj));
            }
            else
            {
                AccessHelper.GetInstance().ExecuteSql(sql, parameters);
            }
        }

        public void UpdateMenuItem(MenuItem item)
        {
            string sql = "update  MenuItem set Title= @Title, Target=@Target, [Index]=@Index, PID=@PID, icon=@icon, IsDefault=@IsDefault where ID=@ID";
            List<SqlParameter> sqlparams = new List<SqlParameter>()
            {
                new SqlParameter(){ParameterName="@Title", Value = item.Title},
                new SqlParameter(){ParameterName="@Target", Value = item.Target},
                new SqlParameter(){ParameterName="@Index", Value = item.Index},
                new SqlParameter(){ParameterName="@PID", Value = item.PID},
                new SqlParameter(){ParameterName="@ID", Value = item.ID},
                new SqlParameter(){ParameterName="@IsDefault", Value= item.IsDefault}
                //new SqlParameter(){ParameterName="@icon", Value = item.icon},
            };
            SqlParameter p = new SqlParameter() { ParameterName = "@icon", Value = item.icon, SqlDbType = System.Data.SqlDbType.Image };
            if (item.icon == null)
                p.Value = DBNull.Value;
            sqlparams.Add(p);
            //if (item.IsDefault && item.PID == 0)
            //{
            //    //SqlConnection conn = null;
            //    //SqlTransaction transaction = AccessHelper.GetInstance().BeginTransaction(out conn);
            //    //SqlCommand cmmd = new SqlCommand(sql, conn, transaction);
            //    //cmmd.Parameters.AddRange(sqlparams.ToArray());
            //    //cmmd.ExecuteNonQuery();
            //    UpdateIsDefault(item.ID);

            //}
            //else
            //{
                AccessHelper.GetInstance().ExecuteSql(sql, sqlparams.ToArray());
            //}
        }

        public void DeleteMenuItem(int id)
        {
            string sql = "delete  MenuItem  where ID=@ID";
            List<SqlParameter> sqlparams = new List<SqlParameter>()
            {                
                new SqlParameter(){ParameterName="@ID", Value = id},
            };
            AccessHelper.GetInstance().ExecuteSql(sql, sqlparams.ToArray());
        }

        /// <summary>
        /// 修改默认主页
        /// </summary>
        /// <param name="id"></param>
        private void UpdateIsDefault(int id)
        {
            string sql = "update MenuItem set IsDefault =0 where PID=0 and ID <> " + id;
            acc.ExcultData(sql);
            //AccessHelper.GetInstance().EndTransaction(transaction);
        }



        public ObservableCollection<MenuItem> GetSubMenuByUserID(int userid, int topid)
        {
            ObservableCollection<MenuItem> menus = new ObservableCollection<MenuItem>();
            string sql = string.Format(@"with Menu as(
	select  m.ID, m.Title, Isnull(m.[Target], '') as [Target], m.[Index], m.PID, m.icon, m.IsDefault
,xiaoxicount=(select COUNT(*) from SystemInfo where 1=1 and MenuId=m.id and status=0 and CreateUserID=(select RoleID from UserInfo where UserID={0}))
from UserInfo u
                        left join Roles r on u.RoleID = r.ID
                        left join RoleMenu rm on rm.RoleID = r.ID
                        left join MenuItem m on m.ID = rm.MenuID and m.PID={1}
                        where u.UserID = {0} and m.ID is not null
	union all 
	select mi.ID, mi.Title, mi.Target, mi.[index], mi.PID, mi.icon, mi.IsDefault 
,xiaoxicount
from Menu m 	
	inner join MenuItem mi on mi.PID = m.id
	inner join RoleMenu rm2 on rm2.MenuID = mi.ID
	inner join Roles r2 on r2.ID = rm2.RoleID
	inner join UserInfo u2 on u2.RoleID = r2.ID
	where u2.UserID ={0}
)
select ID, Title, Isnull(Target, '') as [Target], [index], PID, icon, IsDefault,xiaoxicount from Menu  order by [index]", userid, topid);

      
            DataSet ds = acc.ExcuteDataSet(sql);
            if (ds == null || ds.Tables.Count < 1)
                return null;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                MenuItem item = new MenuItem()
                {
                    ID = int.Parse(dr["ID"].ToString()),
                    Title = dr["Title"].ToString(),
                    Target = dr["Target"].ToString(),
                    Index = int.Parse(dr["Index"].ToString()),
                    PID = int.Parse(dr["PID"].ToString()),
                    icon = dr["icon"] as byte[],
                    IsDefault = Convert.ToBoolean(dr["IsDefault"].ToString()),
                    xiaoxicount = int.Parse(dr["xiaoxicount"].ToString())
                };
                menus.Add(item);

            }
            return menus;
        }
        /// <summary>
        /// 大图标
        /// </summary>
        /// <param name="userid"></param>
        /// <param name="topid"></param>
        /// <returns></returns>
        public ObservableCollection<MenuItem> GetSubMenuByUserID(int userid, int topid,int role)
        {
            ObservableCollection<MenuItem> menus = new ObservableCollection<MenuItem>();
            string sql = string.Format(@"with Menu as(
	select  m.ID, m.Title, Isnull(m.[Target], '') as [Target], m.[Index], m.PID, m.icon, m.IsDefault
,xiaoxicount=(select COUNT(*) from SystemInfo where 1=1 and MenuId=m.id and status=0 and CreateUserID=2 
                and CHARINDEX(CONVERT(varchar(100), OrgIDs),(select OrgCode from UserInfo where UserID={0}))>0)
from UserInfo u
                        left join Roles r on u.RoleID = r.ID
                        left join RoleMenu rm on rm.RoleID = r.ID
                        left join MenuItem m on m.ID = rm.MenuID and m.PID={1}
                        where u.UserID = {0} and m.ID is not null
	union all 
	select mi.ID, mi.Title, mi.Target, mi.[index], mi.PID, mi.icon, mi.IsDefault 
,xiaoxicount
from Menu m 	
	inner join MenuItem mi on mi.PID = m.id
	inner join RoleMenu rm2 on rm2.MenuID = mi.ID
	inner join Roles r2 on r2.ID = rm2.RoleID
	inner join UserInfo u2 on u2.RoleID = r2.ID
	where u2.UserID ={0}
)
select ID, Title, Isnull(Target, '') as [Target], [index], PID, icon, IsDefault,xiaoxicount from Menu  order by [index]", userid, topid);


            DataSet ds = acc.ExcuteDataSet(sql);
            if (ds == null || ds.Tables.Count < 1)
                return null;
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                MenuItem item = new MenuItem()
                {
                    ID = int.Parse(dr["ID"].ToString()),
                    Title = dr["Title"].ToString(),
                    Target = dr["Target"].ToString(),
                    Index = int.Parse(dr["Index"].ToString()),
                    PID = int.Parse(dr["PID"].ToString()),
                    icon = dr["icon"] as byte[],
                    IsDefault = Convert.ToBoolean(dr["IsDefault"].ToString()),
                    xiaoxicount = int.Parse(dr["xiaoxicount"].ToString())
                };
                menus.Add(item);

            }
            return menus;
        }
    }
}
